package com.bonc.ioc.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.Collator;
import java.util.*;

@Slf4j
public class ExcelUtils {

    private final static String EXCEL2003 = "xls";
    private final static String EXCEL2007 = "xlsx";
    private final static Comparator<Object> CHINA_COMPARE = Collator.getInstance(Locale.CHINA);

    public static List<List<String>> readExcel(MultipartFile file) {
        log.info("-----------ExcelUtils--readExcel-------------");
        List<List<String>> list = new LinkedList<>();
        String fileName = file.getOriginalFilename();
        log.info("fileName=" + fileName);
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            log.error("上传文件格式不正确");
        }
        Workbook workbook = null;
        try {
            /*InputStream is = new FileInputStream( java.net.URLDecoder.decode(path,"UTF-8"));
            String fileType = path.substring(path.lastIndexOf("."));
            if (".xls".equals(fileType)) {
                workbook = new HSSFWorkbook(is);
            } else if (".xlsx".equals(fileType)) {
                workbook = new XSSFWorkbook(is);
            }*/
            InputStream is = file.getInputStream();
            workbook = WorkbookFactory.create(is);
            /*if (fileName.endsWith(EXCEL2007)) {
                workbook = new XSSFWorkbook(is);
            }
            if (fileName.endsWith(EXCEL2003)) {
                workbook = new HSSFWorkbook(is);
            }*/
            if (workbook != null) {
                Sheet sheet = workbook.getSheetAt(0);
                int lastRowNum = sheet.getLastRowNum();
                for (int i = 0; i < lastRowNum + 1; i++) {
                    LinkedList<String> strings = new LinkedList<>();
                    Row row = sheet.getRow(i);
                    short lastCellNum = row.getLastCellNum();
                    for (int s = 0; s < lastCellNum; s++) {
                        strings.add(row.getCell(s).toString());
                    }
                    list.add(strings);
                }
            }
        } catch (Exception e) {
            log.error(String.format("parse excel exception!"), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.error(String.format("parse excel exception!"), e);
                }
            }
        }
        return list;
    }

    public static void main(String args[]) {
        /*long t1 = System.currentTimeMillis();
        System.out.println(new File(".").getAbsolutePath());
        List<UserInfo> list = ExcelUtils.readExcel("用户导入模版.xlsx", UserInfo.class,null);
        long t2 = System.currentTimeMillis();
        System.out.println(String.format("read over! cost:%sms", (t2 - t1)));
        if(null!=list && list.size()>0){
            for(int i=0;i<list.size();i++){
                UserInfo userInfo=list.get(i);
                String rowNum=userInfo.getRowNum();
                String userName=userInfo.getUserName();
                String mobile=userInfo.getMobile();
                String email=userInfo.getEmail();
                *//*if(StringUtils.isNotBlank(rowNum)){
                    if(!StringUtils.isNotBlank(loginId) && !StringUtils.isNotBlank(userName) && !StringUtils.isNotBlank(mobile) && !StringUtils.isNotBlank(email)){
                        System.out.println(JSON.toJSON(userInfo));
                    }
                }*//*
                System.out.println(JSON.toJSON(userInfo));
            }
        }*/

        List<String> cityList = new ArrayList<String>();
        cityList.add("上海");
        cityList.add("北京");
        cityList.add("广州");
        cityList.add("深圳");
        cityList.add("ddd");
        cityList.add("1234");

        Collections.sort(cityList, CHINA_COMPARE);
        StringBuffer sb = new StringBuffer();
        sb.append("dd,");
        String temp = sb.toString();
        temp = temp.substring(0, temp.length() - 1);
        //sb.substring(0,sb.length()-1);
        System.out.println(temp);

    }


}
